import pymysql
import urllib.request
import json
from datetime import date, datetime, timedelta

db = pymysql.connect("114.115.160.16","root","123456Aa!","fund")
cursor = db.cursor()

headers = {
    "token":"atTPd9c8sA"
}
idid = 1
rate_id = 1
sql = "select fund_id,category from api_fund"
cursor.execute(sql)
for data in cursor.fetchall():
    fund_id = data[0]
    fund_category = data[1]
    if fund_category != 5:
        continue
    response = urllib.request.urlopen("https://api.doctorxiong.club/v1/fund/position" + "?code=" + fund_id + "&startDate=2021-04-13" +"&token=atTPd9c8sA").read().decode("UTF-8")
    try:
        obj = json.loads(response)["data"]
        stock_list = obj["stockList"]
        for stock in stock_list:
            stock_id = stock[0]
            stock_name = stock[1]
            stock_rate = float(stock[2].strip("%")) / 100
            sql = "insert into api_position values(%d, '%s', '%s', '%s', %f)" % (idid, fund_id, stock_id, stock_name, stock_rate)
            cursor.execute(sql)
           # db.commit()
            idid += 1
        stock_rate = obj["stock"]
        bond_rate = obj["bond"]
        cash_rate = obj["cash"]
        sql = "insert into api_rate values(%d,'%s','%s','%s','%s')" % (rate_id, stock_rate, bond_rate, cash_rate, fund_id)
        rate_id += 1
        cursor.execute(sql)
        #db.commit()
        print("ok!",fund_id)
    except Exception as e:
        print(e,fund_id)
        #print("fail!",obj)
db.commit()